package gui;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class dao {

	private static Connection conn = createConnection();

	public static Connection createConnection() {
		Connection conn = null;
		try {
			String userName = "root";
			String password = "";
			String url = "jdbc:mysql://localhost/dmu";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, userName, password);
			System.out.println("Database connection established");
			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
		} catch (Exception e) {
			System.err.println("Cannot connect to database server: " + e);
		}
		return conn;
	}

	/*
	 * Metoder som bruges for at løse opgaverne
	 */	
	 public static void startTransaction() {
	        try {
	            conn.setAutoCommit(false);
	        }
	        catch (SQLException e) {
	             System.out.println("start");
	             throw new RuntimeException(e.getMessage());
	           
	        }
	    }
	    
	    public static void endTransaction() {
	        try {
	            conn.commit();
	            conn.setAutoCommit(true);
	   
	        }
	        catch (SQLException e) {
	        	System.out.println("commit");
	            System.out.println(e);
	        }
	    }

	/*
	 * Opgave A
	 */
	public static void opretMellemvareA(int id) {
		try {
			startTransaction();
			PreparedStatement p0;
			p0 = conn.prepareStatement("INSERT INTO MELLEMVARE " +
					"SET " +
					"ID = (SELECT MAX(M1.ID)+1 FROM MELLEMVARE M1)," + 
					"NAESTEDELBEHANDLING = 1, " +
					"NAVN = (SELECT NAVN FROM PRODUKTTYPE P1 " +
					"WHERE P1.ID = " + id + "), " +
					"VARENUMMER = (SELECT SENESTEVARENR FROM LAGER), " +
					"BEHANDLING_ID = (SELECT BEHANDLING_ID FROM PRODUKTTYPE P2 " +
					"WHERE P2.ID = " + id + ")");
			p0.executeUpdate();
			Thread.sleep(2000); 
			PreparedStatement p1;
			p1 = conn.prepareStatement("UPDATE LAGER SET SENESTEVARENR = SENESTEVARENR+1 WHERE ID = 1");
			p1.executeUpdate();
			PreparedStatement p2;
			p2 = conn.prepareStatement("INSERT INTO TIMESTAMP "
					+ "SET "
					+ "ID = (SELECT MAX(T1.ID)+1 FROM TIMESTAMP T1),"
					+ "TIDSPUNKT = current_timestamp(),"
					+ "DELBEHANDLING_ID = (SELECT D1.ID FROM MELLEMVARE M1 "
					+ "INNER JOIN DELBEHANDLING D1 "
					+ "ON M1.BEHANDLING_ID = D1.DELBEHANDLINGER_ID "
					+ "WHERE M1.ID = (SELECT MAX(M2.ID) FROM MELLEMVARE M2)),"
					+ "STARTTIDLISTE_ID = (SELECT MAX(M3.ID) FROM MELLEMVARE M3)");
			p2.executeUpdate();
			endTransaction();
			p0.close();
			p1.close();
			p2.close();
		
			
		
			 

		} 
			
	 catch (SQLException e) {
			
			System.err.println(e);
		} catch (InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
		

	/*
	 * Opgave B
	 */

	public static void getAllMellemvarer() {
		try {
			Statement s = conn.createStatement();
			s.executeQuery("SELECT l.pladsnr, m.id, "
					+ "t.starttidliste_id, "
					+ "d.maxtid, "
					+ "((MAX(t.tidspunkt) + d.maxtid)- current_timestamp) as tid_til_max "
					+ "FROM LAGERPLADS l "
					+ "JOIN MELLEMVARE m ON l.mellemvare_id = m.id "
					+ "JOIN TIMESTAMP t ON t.starttidliste_id = m.id "
					+ "JOIN DELBEHANDLING d ON t.delbehandling_id = d.id "
					+ "GROUP BY l.pladsnr " + "ORDER BY tid_til_max");
			ResultSet rs = s.getResultSet();
			while (rs.next()) {
				System.out.println("Pladsnr:\t\t" + rs.getInt("pladsnr"));
				System.out.println("ID:\t\t\t" + rs.getInt("id"));
				System.out.println("Max tid:\t\t" + rs.getInt("maxtid"));
				System.out.println("Tid til max:\t\t"
						+ rs.getInt("tid_til_max"));

				System.out
						.println("############################################");
			}
			System.out.println("done");
		} catch (SQLException e) {
			System.err.println(e);
		}
	}

	
	/*
	 * Opgave C
	 */
	public static void findMinOgMax() {
		try {
			Statement s = conn.createStatement();
			s.executeQuery("SELECT m.id as MellemvareID, "
					+ "SUM(mintid) as minitid, "
					+ "SUM(maxtid) as maxitid "
					+ "FROM MELLEMVARE m "
					+ "INNER JOIN BEHANDLING b on m.behandling_id = b.id "
					+ "INNER JOIN DELBEHANDLING d on b.id = d.delbehandlinger_id "
					+ "GROUP BY m.id");
			ResultSet rs = s.getResultSet();
			while (rs.next()) {
				System.out.println("Mellemvare ID:\t\t"
						+ rs.getInt("MellemvareID"));
				System.out.println("Minimum tid på lager:\t"
						+ rs.getInt("minitid"));
				System.out.println("Maksimum tid på lager:\t"
						+ rs.getInt("maxitid"));
				System.out
						.println("############################################");
			}
		} catch (SQLException e) {
			System.err.println(e);
		}
	}

	
	/*
	 * Opgave D
	 */
	public static void findMellemvarerAfType(String type) {
		try {
			PreparedStatement p;
			p = conn.prepareStatement("SELECT * FROM MELLEMVARE m WHERE m.navn = ?");
			p.setString(1, type);
			ResultSet rs = p.executeQuery();
			System.out.println("Finder alle mellemvarer af typen " + type
					+ ":\n--------------------------------------------");

			while (rs.next()) {
				System.out.println("ID:\t\t\t" + rs.getInt("ID"));
				System.out.println("Næste delbehandling:\t"
						+ rs.getInt("NAESTEDELBEHANDLING"));
				System.out.println("Navn:\t\t\t" + rs.getString("NAVN"));
				System.out.println("Varenummer:\t\t" + rs.getInt("VARENUMMER"));
				System.out.println("BehandlingsID:\t\t"
						+ rs.getInt("BEHANDLING_ID"));
				System.out
						.println("############################################");
			}
			System.out.println("done");

		} catch (SQLException e) {
			System.err.println(e);
		}
	}

	
	/*
	 * Opgave E
	 */
	public static void opretMellemvareE(int id) {
		try {		
			Statement s = conn.createStatement();
			s.executeQuery("SELECT * FROM PRODUKTTYPE WHERE ID = " + id );
			ResultSet rs = s.getResultSet();
			if ( !rs.next()){
				throw new RuntimeException("Produkttypen findes ikke");
				
			}
			
			PreparedStatement p0;
			p0 = conn.prepareStatement("INSERT INTO MELLEMVARE " +
					"SET " +
					"ID = (SELECT MAX(M1.ID)+1 FROM MELLEMVARE M1)," + 
					"NAESTEDELBEHANDLING = 1, " +
					"NAVN = (SELECT NAVN FROM PRODUKTTYPE P1 " +
					"WHERE P1.ID = " + id + "), " +
					"VARENUMMER = (SELECT SENESTEVARENR FROM LAGER), " +
					"BEHANDLING_ID = (SELECT BEHANDLING_ID FROM PRODUKTTYPE P2 " +
					"WHERE P2.ID = " + id + ")");
			p0.executeUpdate();
			Thread.sleep(2000); 
			PreparedStatement p1;
			p1 = conn.prepareStatement("UPDATE LAGER SET SENESTEVARENR = SENESTEVARENR+1 WHERE ID = 1");
			p1.executeUpdate();
			PreparedStatement p2;
			p2 = conn.prepareStatement("INSERT INTO TIMESTAMP "
					+ "SET "
					+ "ID = (SELECT MAX(T1.ID)+1 FROM TIMESTAMP T1),"
					+ "TIDSPUNKT = current_timestamp(),"
					+ "DELBEHANDLING_ID = (SELECT D1.ID FROM MELLEMVARE M1 "
					+ "INNER JOIN DELBEHANDLING D1 "
					+ "ON M1.BEHANDLING_ID = D1.DELBEHANDLINGER_ID "
					+ "WHERE M1.ID = (SELECT MAX(M2.ID) FROM MELLEMVARE M2)),"
					+ "STARTTIDLISTE_ID = (SELECT MAX(M3.ID) FROM MELLEMVARE M3)");
			p2.executeUpdate();
			p0.close();
			p1.close();
			p2.close();
		} 
			
	 catch (SQLException e) {
			
			System.err.println(e);
		} catch (InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/*
	 * Opgave F
	 */
	public static void cleanUp(int antal) {
		try {
			PreparedStatement p;
			p = conn.prepareStatement("CALL cleanUp(?)");
			p.setInt(1, antal);
			p.executeUpdate();
			p.close();
		} catch (SQLException e) {
			System.err.println("En eller flere af disse \n mellemvare står på en lagerplads, \n flyt venlist gamle vare fra lageret " + e);
		}
	}

}
